package youleche.com.sql.servlet.common;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import youleche.com.sql.business.BTableField;
import youleche.com.sql.business.BTableInfo;
import youleche.com.sql.model.MTableField;
import youleche.com.sql.model.MTableInfo;
import youleche.com.util.DateUtil;

/**
 * sql生成工厂
 * @author Administrator
 *
 */
public class SqlGenerateFactory {
	/**
	 * 生成表对象
	 * @param fields 字段集合
	 * @return 返回sql
	 * @throws Exception 
	 */
	public static String generateObjectByFields(List<MTableField> fields) throws Exception{
		String tableId = fields.get(0).getTableId();
		BTableInfo tableinfoHandler = new BTableInfo();
		MTableInfo tableInfo = tableinfoHandler.getTableInfoByTableId(tableId);
		StringBuilder sb = new StringBuilder();
		boolean isContainsDate = false;
		for (MTableField m : fields) {
			if("date".equals(m.getFieldType()) || "datetime".equals(m.getFieldType())){
				isContainsDate = true;
				break;
			}
		}
		sb.append("package youleche.com.sql.model;");
		sb.append("<br/><br/>");
		if(isContainsDate == true){
			sb.append("import java.util.Date;");
		}
		
		sb.append("<br/><br/>");
		sb.append("/**");
		sb.append("<br/>");
		sb.append("&nbsp; * " + tableInfo.getTableDesc());
		sb.append("<br/>");
		sb.append("&nbsp; * @author 优乐车");
		sb.append("<br/>");
		sb.append("&nbsp; *");
		sb.append("<br/>");
		sb.append("&nbsp; */");
		sb.append("<br/>");
		String tableName = tableInfo.getTableName();
		sb.append("public class M" + tableName.toUpperCase().substring(0, 1) + tableName.substring(1, tableName.length()) + " {");
		sb.append("<br/>");
		for (MTableField m : fields) {
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;/**");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * " + m.getFieldChineseName());
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */");
			sb.append("<br/>");
			String javaType = convertToJavaType(m.getFieldType());
			String englishName =  m.getFieldEnglishName();
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;private " + javaType + " " + m.getFieldEnglishName() + ";<br/>");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;/**");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * 获取" + m.getFieldChineseName());
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * @return " + m.getFieldChineseName());
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;public " + javaType + " get" + englishName.toUpperCase().substring(0, 1) + englishName.substring(1, englishName.length()) + "() {");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return this." + m.getFieldEnglishName() + ";");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;}");
			sb.append("<br/>");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;/**");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * 设置" + m.getFieldChineseName());
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * @param " + m.getFieldEnglishName() + " " + m.getFieldChineseName());
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; */");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;public void set" +englishName.toUpperCase().substring(0, 1) + englishName.substring(1, englishName.length()) + "(" + javaType +" " + m.getFieldEnglishName() + ") {");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this." + m.getFieldEnglishName() + " = " + m.getFieldEnglishName() + ";");
			sb.append("<br/>");
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;}");
			sb.append("<br/>");
			sb.append("<br/>");
		}
		sb.append("}");
		
		return sb.toString();
	}
	
	/**
	 * 数据库类型转换为Java类型
	 * @param fieldType 字段类型
	 * @return Java类型
	 */
	private static String convertToJavaType(String fieldType){
		if("varchar".equals(fieldType)){
			return "String";
		}else if("datetime".equals(fieldType)){
			return "Date";
		}else if("tinyint".equals("fieldType")){
			return "int";
		}
		
		return "String";
	}
	
	/**
	 * 生成表sql
	 * @param fields 字段集合
	 * @return 返回sql
	 * @throws Exception 
	 */
	public static String generateSqlByFields(List<MTableField> fields) throws Exception{
		String tableId = fields.get(0).getTableId();
		BTableInfo tableinfoHandler = new BTableInfo();
		MTableInfo tableInfo = tableinfoHandler.getTableInfoByTableId(tableId);
		StringBuilder sb = new StringBuilder();
		sb.append("/****** 对象:  Table ");
		sb.append(tableInfo.getTableName());
		sb.append("   脚本日期: ");
		sb.append(DateUtil.getDate("yyyy-MM-dd HH:mm:ss"));
		sb.append(" ******/");
		sb.append("<br/>");
		sb.append("/****** 优乐车在线 ******/");
		sb.append("<br/>");
		sb.append("DROP TABLE IF EXISTS `");
		sb.append(tableInfo.getTableName());
		sb.append("`;");
		sb.append("<br/>");
		sb.append("CREATE TABLE `");
		sb.append(tableInfo.getTableName());
		sb.append("` (");
		sb.append("<br/>");
		String fieldType = "";
		for (MTableField model : fields) {
			sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
			sb.append("`");
			sb.append(model.getFieldEnglishName());
			sb.append("` ");
			fieldType  = model.getFieldType();
			if("datetime".equals(fieldType) || "text".equals(fieldType)){
				sb.append(fieldType + " ");
			}else{
				sb.append(fieldType);
				sb.append("(");
				sb.append(model.getFieldLength());
				sb.append(") ");				
			}
			sb.append(model.getIsNull() == 0 ? "NULL " : "NOT NULL ");
			
			if("tinyint".equals(fieldType) || "int".equals(fieldType)){
				sb.append("DEFAULT '0' ");
			}else if("datetime".equals(fieldType)){
				sb.append("DEFAULT '1990-01-01 00:00:00' ");
			}else{
				sb.append("DEFAULT '' ");
			}
			sb.append("COMMENT '");
			sb.append(model.getFieldChineseName());
			sb.append("',");
			sb.append("<br/>");
		}
		
		for (MTableField model : fields) {
			if(model.getIsPrimaryKey() == 1){
				sb.append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY ( `" + model.getFieldEnglishName() + "` ),");
				break;
			}
		}
		
		sb.delete(sb.length() - 1, sb.length());
		sb.append("<br/>");
		sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='");
		sb.append(tableInfo.getTableDesc());
		sb.append("'");
		if(!sb.toString().contains("PRIMARY KEY")){
			return "请设计主键";
		}
		
		return sb.toString();
	}
	
	/**
	 * 解析建表sql
	 * @param sql
	 * @return 返回是否解析正确
	 */
	public static boolean parseCreateTableSql(String sql){
		String tableName = getTableName(sql).replace("`", "");
		addTableInfo(tableName, getTableDesc(sql).replace("'", "").replace(";", ""));
		addTableField(parseSqlToTableField(sql, getTableId(tableName)));
		return true;
	}
	
	/**
	 * 根据表名获取表id
	 * @param tableName 表名
	 * @return 表id
	 */
	private static String getTableId(String tableName){
		BTableInfo handler = new BTableInfo();
		try{
			return handler.getTableIdByTableName(tableName);
		}catch(Exception ex){
			return "";
		}
	}
	
	/**
	 * 添加字段
	 * @param list 字段集合
	 */
	private static void addTableField(List<MTableField> list){
		if(list == null || list.size() == 0){
			return;
		}
		
		BTableField handler = new BTableField();
		try {
			handler.addTableFields(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 解析出字段
	 * @param sql sql语句
	 * @return 返回字段集合
	 */
	private static List<MTableField> parseSqlToTableField(String sql, String tableId ){
		String fieldsStrRegex = "\\`[A-Za-z0-9]*\\`\\s[A-Za-z]*[(]?[\\d]*[)]?\\s[A-Za-z]{1,}\\s[\\w\\W]*[\\,|\\)]";
		Pattern p = Pattern.compile(fieldsStrRegex);
		Matcher m = p.matcher(sql);
		String fieldsStr = "";
		while(m.find()){
			fieldsStr = m.group(0);
			break;
		}
		
		String[] fieldsArr = fieldsStr.split(",");
		List<MTableField> list = new LinkedList<MTableField>();
		String primaryKey = getPrimaryKey(sql);
		MTableField field = null;
		for (String string : fieldsArr) {
			field = new MTableField();
			String englishName = getFiledEnglishName(string);
			if(primaryKey.equals(englishName)){
				field.setIsPrimaryKey(1);
			}
			field.setTableId(tableId);
			field.setFieldEnglishName(englishName);
			field.setFieldChineseName(getFiledChineseName(string));
			field.setFieldType(getFieldType(string));
			field.setFieldLength(getFieldLength(string));
			list.add(field);
		}
		
		return list;
	}
	
	/**
	 * 获取字段类型
	 * @param fieldSql
	 * @return
	 */
	private static String getFieldType(String fieldSql){
		Pattern p = Pattern.compile("\\`\\s{0,}([a-zA-Z0-9]*)");
		Matcher m = p.matcher(fieldSql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	/**
	 * 获取字段长度
	 * @param fieldSql
	 * @return
	 */
	private static String getFieldLength(String fieldSql){
		Pattern p = Pattern.compile("\\`\\s{0,}[a-zA-Z0-9]*\\(([0-9]{0,})\\)");
		Matcher m = p.matcher(fieldSql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	/**
	 * 获取字段英文名
	 * @param fieldSql
	 * @return
	 */
	private static String getFiledEnglishName(String fieldSql){
		
		Pattern p = Pattern.compile("\\`([a-zA-Z0-9]*)\\`");
		Matcher m = p.matcher(fieldSql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	/**
	 * 获取字段中文名
	 * @param fieldSql
	 * @return
	 */
	private static String getFiledChineseName(String fieldSql){
		
		Pattern p = Pattern.compile("COMMENT\\s\'([\\w\\W]*)\'");
		Matcher m = p.matcher(fieldSql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	
	
	/**
	 * 根据sql获取主键
	 * @param sql sql语句
	 * @return 返回主键
	 */
	private static String getPrimaryKey(String sql){
		Pattern p = Pattern.compile("PRIMARY KEY\\s\\(\\`([A-Za-z0-9]{1,})\\`\\)");
		Matcher m = p.matcher(sql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	/**
	 * 添加表信息
	 * @param tableName 表名
	 * @param tableDesc 表说明
	 */
	private static void addTableInfo(String tableName, String tableDesc){
		MTableInfo info = new MTableInfo();
		info.setTableName(tableName);
		info.setCreateUser("sql导入");
		info.setCreateTime(DateUtil.getDate());
		info.setTableDesc(tableDesc);
		info.setDatabase("youleche");
		BTableInfo handler = new BTableInfo();
		try {
			if(!handler.isExistTableName(tableName)){
				handler.addTableInfo(info);				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取表名
	 * @param sql
	 * @return
	 */
	private static String getTableName(String sql){
		Pattern p = Pattern.compile("CREATE TABLE (.*?) ");
		Matcher m = p.matcher(sql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	/**
	 * 获取表说明
	 * @param sql
	 * @return
	 */
	private static String getTableDesc(String sql){
		Pattern p = Pattern.compile("COMMENT=([\\w\\W]*)");
		Matcher m = p.matcher(sql);
		while(m.find()){
			return m.group(1);
		}
		
		return "";
	}
	
	
}
